import sqlite3
import traceback

from sqlalchemy import create_engine
import shutil
import pandas
import os
from reptile.obtain import WorkPath


class Db:
    db_path = r'\db\management_db.db'
    path = WorkPath.work_dir + db_path  # r'F:\pycharm_projects\management\db\management_Db.Db'
    connect = sqlite3.connect(path)

    @classmethod
    def change(cls, path=r'F:\pycharm_projects\management\db\management_Db.Db'):
        """改变数据库连接路径,输入path"""
        try:
            cls.path = path
            cls.connect = sqlite3.connect(path)
        except:
            print("未知错误")

    def __init__(self, path=r'F:\pycharm_projects\management\db\management_Db.Db'):
        try:
            self.path = path
            self.connect = sqlite3.connect(path)
        except:
            print("未知错误")

    @staticmethod
    def backup(target_file, copy_to, newname='backup.Db', oldname='management_Db.Db'):
        """默认输入两个参数，如果需要重命名文件则输入四个参数：target_file(目标文件路径)，
        copy_to（复制到该路径）,newname,oldname注意文件后缀"""
        if not os.path.exists(copy_to):
            os.makedirs(copy_to)
        try:
            shutil.copy(target_file, copy_to)
            os.rename(f'{copy_to}\\{oldname}', f'{copy_to}\\{newname}')
            return True
        except IOError:
            print("无法将文件复制到目标路径")
            return False
        except:
            print("未知错误")
            return False

    @staticmethod
    def import_csv(path, table_name, coding='utf-8'):
        """path(导入文件的路径),table_name(表名字),coding(编码方式)"""
        try:
            df = pandas.read_csv(path, encoding=coding)
            df.to_sql('temp', Db.connect, if_exists='append', index=False)
        except IndentationError:
            print("没有找到文件")
            return False
        if table_name.startswith('score'):
            sql_cre = f'CREATE TABLE "{table_name}"' \
                      f'("snum"	TEXT COLLATE BINARY,"cnum"	INTEGER,"score"	INTEGER,PRIMARY KEY("snum","cnum"),' \
                      f'FOREIGN KEY("snum") REFERENCES "student"("snum"))'
        elif table_name.startswith('student'):
            sql_cre = f'CREATE TABLE "{table_name}" ' \
                      f'("snum"	TEXT COLLATE BINARY,"sname"	TEXT NOT NULL,"sdept"	' \
                      f'TEXT NOT NULL,"sclass"	INTEGER NOT NULL,"sexual"	' \
                      f'TEXT NOT NULL DEFAULT "男","code"	' \
                      'TEXT NOT NULL,PRIMARY KEY("snum"))'
        elif table_name.startswith('teacher'):
            sql_cre = f'CREATE TABLE "{table_name}" ("tnum"	TEXT NOT NULL,"tname"	' \
                      f'TEXT NOT NULL,"tdept"	TEXT NOT NULL,"cnum"	' \
                      f'INTEGER NOT NULL,"age"	INTEGER NOT NULL,"sexual"	TEXT NOT NULL,"code"	T' \
                      f'EXT NOT NULL,PRIMARY KEY("tnum","tdept","cnum"),FOREIGN KEY("cnum") REFERENCES "course")'
        elif table_name.startswith('academic'):
            sql_cre = f'CREATE TABLE "{table_name}" ("anum"	TEXT NOT NULL,"aname"	' \
                      f'TEXT NOT NULL,"age"	INTEGER NOT NULL,"sexual"	TEXT NOT NULL,"code"	' \
                      f'TEXT NOT NULL,PRIMARY KEY("anum"))'
        elif table_name.startswith('course'):
            sql_cre = f'CREATE TABLE "{table_name}" ("cnum"	' \
                      f'INTEGER NOT NULL,"cname"	TEXT NOT NULL,"ctime"	' \
                      f'INTEGER NOT NULL,"cplace"	TEXT,PRIMARY KEY("cnum"))'
        else:
            return False
        sql_imp = f"insert into {table_name} select * from temp"
        sql_drop = f"drop table temp"
        try:
            cur = Db.connect.cursor()
            cur.execute(sql_cre)
            cur.execute(sql_imp)
            cur.execute(sql_drop)
            Db.connect.commit()
            cur.close()
            return True
        except:
            traceback.print_exc()
            print("未知错误")
            return False

    @staticmethod
    def export_csv(table, csv_path):
        """csv_path应该包含csv文件名和后缀"""
        try:
            engine = create_engine("sqlite:///" + Db.path, echo=True)
            table = pandas.read_sql_table(table_name=table, con=engine)
            fp = open(csv_path, 'w')
            fp.close()
            table.to_csv(csv_path, sep=',', index=False)
            return True
        except:
            print("未知错误")
            return False

    @staticmethod
    def get_field_name(table_name):
        """输入table_name(str),返回一个表的所有列名（list）"""
        cur = Db.connect.cursor()
        # 获取表名，保存在tab_name列表
        sql_name = f"select name from sqlite_master where type='table' and name='{table_name}'"
        cur.execute(sql_name)
        Db.connect.commit()
        tab_name = cur.fetchall()
        tab_name = [line[0] for line in tab_name]
        col_names = []
        for line in tab_name:
            cur.execute('pragma table_info({})'.format(line))
            col_name = cur.fetchall()
            col_name = [x[1] for x in col_name]
            col_names.append(col_name)
        cur.close()
        return col_names[0]

    @staticmethod
    def get_all_tableName():
        """返回所有表名"""
        cur = Db.connect.cursor()
        sql_name = f"select name from sqlite_master where type='table'"
        cur.execute(sql_name)
        Db.connect.commit()
        table_name = cur.fetchall()
        table_name = [line[0] for line in table_name]
        return table_name
